﻿<!DOCTYPE html PUBLIC "-//W3C//DTD XHTML 1.0 Transitional//EN" "http://www.w3.org/TR/xhtml1/DTD/xhtml1-transitional.dtd">
<html xmlns="http://www.w3.org/1999/xhtml" xml:lang="utf-8" lang="utf-8">
<head>
<meta http-equiv="Content-Type" content="text/html; charset=utf-8" />
<title>Dz2Babel DB Converter</title>
<!-- stylesheet -->
<style>
.box{border: 2px solid #DCD9D0; width: 800px; height: 150px; overflow: auto}
.bold{font-weight: bold;}
.warnning{color: #BC4E2A;margin-bottom: 5px}
</style>
</head>
<body>
<?php
// Define root path
define('ROOTPATH', __FILE__);

// Include ezSQL core
include_once 'ezsql/shared/ez_sql_core.php';
// Include ezSQL database specific component
include_once 'ezsql/mysql/ez_sql_mysql.php';
// Include common functions
include_once 'includes/functions.php';

@session_start();

if ( isset($_POST['setup']) && $_POST['setup']=='1' )	
{
	echo '<h1>Discuz7 to Project Babel 0.6 Database Converter</h1>';

	// Discuz database configuretions
	$dz_config = array
	(
		'host' => $_POST['source_host'],		// databse address.(default is localhost)
		'user' => $_POST['source_user'],			// user name
		'password' => $_POST['source_password'],			// passowrd
		'database' => $_POST['source_database'],		// database name
		'prefix' => $_POST['source_prefix'],			// database prefix
	);
	
	// babel database configuretions
	$babel_config = array
	(
		'host' => $_POST['target_host'],		// databse address.(default is localhost)
		'user' => $_POST['target_user'],			// user name
		'password' => $_POST['target_password'],			// passowrd
		'database' => $_POST['target_database'],		// database name
		'prefix' => $_POST['target_prefix'],		// database prefix
	);

	@mysql_connect($dz_config['host'], $dz_config['user'], $dz_config['password']) or 
		die ('Discuz 7 database can\'t connect!');
		
	@mysql_connect($babel_config['host'], $babel_config['user'], $babel_config['password']) or 
		die ('Babel 0.6 database can\'t connect!');
	
	// Connect database
	$dz_db = new ezSQL_mysql($dz_config['user'], $dz_config['password'], $dz_config['database'], $dz_config['host']);
	$babel_db = new ezSQL_mysql($babel_config['user'], $babel_config['password'], $babel_config['database'], $babel_config['host']);
	
	// Using charset utf8
	$dz_db->query("SET NAMES 'utf8'");
	$dz_db->query("SET CHARACTER SET 'utf8'");
	
	$babel_db->query("SET NAMES 'utf8'");
	$babel_db->query("SET CHARACTER SET 'utf8'");
	
	
	/////// Users ////////
	$dz_users = $dz_db->get_results("SELECT * FROM {$dz_config['prefix']}members", ARRAY_A); //LIMIT 1, 5

	if (sizeof($dz_users)>0)
	{
		// Check users total in current database of babel
		$user_total = $babel_db->get_var("SELECT max(usr_id) FROM {$babel_config['prefix']}user");
		$gid = $user_total;
		
		echo 'Starting Convert users('.sizeof($dz_users).')...<br /><div class="box">';
		$count = 0;
		foreach ( $dz_users as $user )
		{
			$gid++;
			$result = $babel_db->query
			(
				"SELECT * FROM {$babel_config['prefix']}user
				WHERE usr_email='{$user['email']}'"
			);
			
			// goto next record if it found user in babel database
			if ( $result!=0 ) 
			{
				echo '<small>'.$gid.': user "'.$user['username'].'" was existed in database.</small><br />';
				continue;
			}
			
			//User password encrypt
			//***** Discuz7 *******
			//md5(md5($str).$salt);
			//******* punbb ******* 
			//sha1($salt.sha1($str));
			//******* babel ******* 
			//sha1($str);
			$password = random_key(8, true);
			$pwd_hash = sha1($password);
			$babel_db->query
			(
				"INSERT INTO {$babel_config['prefix']}user(
					usr_id, usr_gid, usr_nick, usr_password, usr_email, usr_gender, usr_money,
					usr_geo, usr_created, usr_lastupdated, usr_lastlogin
				)VALUES(
					$gid, $gid, '{$user['username']}', '{$pwd_hash}', '{$user['email']}', '{$user['gender']}', 2000,
					'earth', {$user['regdate']}, {$user['lastpost']}, {$user['lastvisit']}
				)"
			);
			echo '<small class="bold">'.$gid.': insert user "'.$user['username'].'" and password "'.$password.'" successful.</small><br />';
			
			$users[$count]['name'] = $user['username'];
			$users[$count]['password'] = $password;
			$users[$count]['email'] = $user['email'];
			write_file('user.txt', $user['username'].','.$user['email'].','.$password, dirname(ROOTPATH));
			$count++;
		}
		
		// Reset AUTO_INCREMENT is last record +1.
		$user_max = $babel_db->get_var("SELECT max(usr_id) FROM {$babel_config['prefix']}user");
		$next = $user_max++;
		$babel_db->query
		(
			"ALTER TABLE {$babel_config['prefix']}user AUTO_INCREMENT={$next} ROW_FORMAT=DYNAMIC"
		);
		
		if ( $count!=0 )
		{
			$_SESSION['users'] = $users;
		}
		echo '</div>Finished Convert users. Totals: '.$count.'<br /><br />';
	}
	else
	{
		echo '<div class="warnning">empty users in database</div>';
	}

	
	/////// categories ////////
	$dz_cats = $dz_db->get_results("SELECT * FROM {$dz_config['prefix']}forums", ARRAY_A); //LIMIT 1, 5
	if (sizeof($dz_cats)>0)
	{	
		// Get categories total in current database of babel
		$cat_total = $babel_db->get_var("SELECT max(nod_id) FROM {$babel_config['prefix']}node");
		$cid = $cat_total;
	
		echo 'Starting Convert Categories...<br /><div class="box">';		
		$count = 0;
		foreach( $dz_cats as $cat )
		{
			if ( preg_match('/\t(\d{10})\t/', $cat['lastpost'], $match) )
			{
				$last_date = $match[1];
			}
			else
			{
				$last_date = 0;
			}
			
			$cid++;
			if ( $cat['type']=='group' )
			{
				
				$result = $babel_db->query
				(
					"SELECT * FROM {$babel_config['prefix']}node
					WHERE nod_title='{$cat['name']}'"
				);
	
				// goto next record if it found categorie in babel database
				if ( $result!=0 ) 
				{
					echo '<small>'.$cid.': Categorie "'.$cat['name'].'" was existed in database.</small><br />';
					continue;
				}
			
				$babel_db->query
				(
					"INSERT INTO {$babel_config['prefix']}node(
						nod_id, nod_pid, nod_uid, nod_sid, nod_level,
						nod_name, nod_title, nod_topics, nod_lastupdated
					)VALUES(
						{$cid}, 5, 1, 5, 1,
						{$cid}, '{$cat['name']}', {$cat['threads']}, {$last_date}
					)"
				);
				echo '<small class="bold">'.$cid.': insert categorie "'.$cat['name'].'" successful.</small><br />';
				
				$count++;
			}
		}
		
		// Reset AUTO_INCREMENT is last record +1.
		$node_max = $babel_db->get_var("SELECT max(nod_id) FROM {$babel_config['prefix']}node");
		$next = $node_max++;
		$babel_db->query
		(
			"ALTER TABLE {$babel_config['prefix']}node AUTO_INCREMENT={$next} ROW_FORMAT=DYNAMIC"
		);
		
		echo '</div>Finished Convert categories. Total: '.$count.'<br /><br />';
	}
	else
	{
		echo '<div class="warnning">empty categories in database</div>';
	}
	
	/////// forum ////////
	// Still using results of categories table
	if (sizeof($dz_cats)>0)
	{
		$forum_total = $babel_db->get_var("SELECT max(nod_id) FROM {$babel_config['prefix']}node");
		$fid = $forum_total;
		
		echo 'Starting Convert Forums...<br /><div class="box">';
		$count = 0;
		foreach( $dz_cats as $cat )
		{
			$fid++;
			if ( $cat['type']=='forum' )
			{
				$result = $babel_db->query
				(
					"SELECT * FROM {$babel_config['prefix']}node
					WHERE nod_title='{$cat['name']}'"
				);
			
				// goto next record if it found categorie in babel database
				if ( $result!=0 ) 
				{
					echo '<small>'.$fid.': Categorie "'.$cat['name'].'" was existed in database.</small><br />';
					continue;
				}
				
				$cat_title = $dz_db->get_var("SELECT name FROM {$dz_config['prefix']}forums WHERE fid={$cat['fup']}");
				$cid = $babel_db->get_var("SELECT nod_id FROM {$babel_config['prefix']}node WHERE nod_title='{$cat_title}'");
				
				$babel_db->query
				(
					"INSERT INTO {$babel_config['prefix']}node(
						nod_id, nod_pid, nod_uid, nod_sid, nod_level,
						nod_name, nod_title, nod_topics, nod_lastupdated
					)VALUES(
						{$fid}, {$cid}, 1, {$cid}, 2,
						{$fid}, '{$cat['name']}', {$cat['threads']}, {$last_date}
					)"
				);
				echo '<small class="bold">'.$fid.': insert forum "'.$cat['name'].'" successful.</small><br />';
				
				$count++;
			}
		}
		
		// Reset AUTO_INCREMENT is last record +1.
		$node_max = $babel_db->get_var("SELECT max(nod_id) FROM {$babel_config['prefix']}node");
		$next = $node_max++;
		$babel_db->query
		(
			"ALTER TABLE {$babel_config['prefix']}node AUTO_INCREMENT={$next} ROW_FORMAT=DYNAMIC"
		);

		echo '</div>Finished Convert Forums. Total: '.$count.'<br /><br />';
	}
	else
	{
		echo '<div class="warnning">empty forums in database</div>';
	}
	
	///////// topics /////////
	$dz_topics = $dz_db->get_results
	(
		"SELECT 
			{$dz_config['prefix']}posts.message, 
			{$dz_config['prefix']}threads.* 
		FROM {$dz_config['prefix']}threads, {$dz_config['prefix']}posts 
		WHERE 
			{$dz_config['prefix']}threads.tid={$dz_config['prefix']}posts.tid AND
			{$dz_config['prefix']}posts.first=1",
		ARRAY_A
	); //LIMIT 1, 5

	if (sizeof($dz_topics)>0)
	{			
		$topic_total = $babel_db->get_var("SELECT max(tpc_id) FROM {$babel_config['prefix']}topic");
		$tid = $topic_total;
		
		echo 'Starting Convert Topics...<br /><div class="box">';
		$count = 0;
		foreach( $dz_topics as $topic )
		{
			$tid++;
			$result = $babel_db->query
			(
				"SELECT * FROM {$babel_config['prefix']}topic
				WHERE tpc_title='{$topic['subject']}'"
			);
		
			// goto next record if it found categorie in babel database
			if ( $result!=0 ) 
			{
				echo '<small>'.$tid.': topic "'.$topic['subject'].'" was existed in database.</small><br />';
				continue;
			}
			
			$cat_title = $dz_db->get_var("SELECT name FROM {$dz_config['prefix']}forums WHERE fid={$topic['fid']}");
			$cid = $babel_db->get_var("SELECT nod_id FROM {$babel_config['prefix']}node WHERE nod_title='{$cat_title}'");

			$usr_email = $dz_db->get_var("SELECT email FROM {$dz_config['prefix']}members WHERE uid={$topic['authorid']}");
			$uid = $babel_db->get_var("SELECT usr_id FROM {$babel_config['prefix']}user WHERE usr_email='{$usr_email}'");

			if ( empty($uid) )
			{
				continue;
			}

			$message = escape(dz2babel_bbcode($topic['message']));
			$babel_db->query
			(
				"INSERT INTO {$babel_config['prefix']}topic(
					tpc_id, tpc_pid, tpc_uid, 
					tpc_title, tpc_content, tpc_hits, tpc_posts, 
					tpc_created, tpc_lastupdated, tpc_lasttouched
				)VALUES(
					{$tid}, {$cid}, {$uid}, 
					'{$topic['subject']}', '{$message}', {$topic['views']}, {$topic['replies']}, 
					 {$topic['dateline']}, {$topic['lastpost']}, {$topic['lastpost']}
				)"
			);
			echo '<small class="bold">'.$tid.': insert topic "'.$topic['subject'].'" successful.</small><br />';
			
			$count++;
		}
		
		// Reset AUTO_INCREMENT is last record +1.
		$topic_max = $babel_db->get_var("SELECT max(tpc_id) FROM {$babel_config['prefix']}topic");
		$next = $topic_max++;
		$babel_db->query
		(
			"ALTER TABLE {$babel_config['prefix']}topic AUTO_INCREMENT={$next} ROW_FORMAT=DYNAMIC"
		);
		echo '</div>Finished Convert Topics. Total: '.$count.'<br /><br />';
	}
	else
	{
		echo '<div class="warnning">empty topics in database</div>';
	}
	
	/////// posts ////////
	$dz_posts = $dz_db->get_results
	(
		"SELECT 
			{$dz_config['prefix']}posts.*,
			{$dz_config['prefix']}threads.subject title,
			{$dz_config['prefix']}threads.lastpost
		FROM {$dz_config['prefix']}threads, {$dz_config['prefix']}posts 
		WHERE 
			{$dz_config['prefix']}threads.tid={$dz_config['prefix']}posts.tid AND
			{$dz_config['prefix']}posts.first=0",
		ARRAY_A
	); //LIMIT 1, 5

	if (sizeof($dz_posts)>0)
	{			
		$post_total = $babel_db->get_var("SELECT max(pst_id) FROM {$babel_config['prefix']}post");
		$pid = $tid = ($post_total!=0)?$post_total:0;
		
		echo 'Starting Convert Topics...<br /><div class="box">';
		$count = 0;
		foreach( $dz_posts as $post )
		{
			$pid++;
			$result = $babel_db->query
			(
				"SELECT * FROM {$babel_config['prefix']}post
				WHERE pst_created='{$post['dateline']}'"
			);
		
			// goto next record if it found categorie in babel database
			if ( $result!=0 ) 
			{
				echo '<small>'.$pid.': posts "'.$post['title'].'" was existed in database.</small><br />';
				continue;
			}
			
			$topic_dataline = $dz_db->get_var("SELECT dateline FROM {$dz_config['prefix']}threads WHERE tid={$post['tid']}");
			$tid = $babel_db->get_var("SELECT tpc_id FROM {$babel_config['prefix']}topic WHERE tpc_created={$topic_dataline}");

			$usr_email = $dz_db->get_var("SELECT email FROM {$dz_config['prefix']}members WHERE uid='{$post['authorid']}'");
			$uid = $babel_db->get_var("SELECT usr_id FROM {$babel_config['prefix']}user WHERE usr_email='{$usr_email}'");

			if ( empty($uid) )
			{
				continue;
			}
			$subject = escape('Re: '.$post['title']);
			$message = escape(dz2babel_bbcode($post['message']));
			$babel_db->query
			(
				"INSERT INTO {$babel_config['prefix']}post(
					pst_id, pst_tid, pst_uid, 
					pst_title, pst_content,
					pst_created, pst_lastupdated
				)VALUES(
					{$pid}, {$tid}, {$uid}, 
					'{$subject}', '{$message}',
					 {$post['dateline']}, {$post['lastpost']}
				)"
			);
			echo '<small class="bold">'.$pid.': insert post "'.$post['title'].'" successful.</small><br />';
			
			$count++;
		}
		
		// Reset AUTO_INCREMENT is last record +1.
		$post_max = $babel_db->get_var("SELECT max(pst_id) FROM {$babel_config['prefix']}post");
		$next = $post_max++;
		$babel_db->query
		(
			"ALTER TABLE {$babel_config['prefix']}post AUTO_INCREMENT={$next} ROW_FORMAT=DYNAMIC"
		);
		echo '</div>Finished Convert Posts. Total: '.$count.'<br /><br />';
	}
	else
	{
		echo '<div class="warnning">empty posts in database</div>';
	}

	//$dz_db->debug();

	echo '<b>Finished All Convert!</b><br /><hr />';
?>
<div class="setting">
	<h1>Sending new password email to impored users</h1>
	<p>
		It must change other new password for old users because they used encryption algorithm of each forum system.
	</p>
	
	<form method="POST" action="<?php echo $_SERVICE; ?>">
	<p>
		<h2>Sending informations</h2>
		<input type="text" name="from_name" value="Website Name" />
		<input type="text" name="from_email" value="noreply@example.com" />
		<input type="text" name="subject" value="Email Subject" /><br />
<textarea name="content" style="width:500px;height:200px">
你好，{user}! 最近我们更改换了新的论坛系统，由于系统的安全系数原因，我们重设了用户的登录密码，请使用新的密码登录并更改为你熟悉的密码： {password} 。新的论坛网址：http://.com/  感谢你对我们的支持！
</textarea>
	</p>
	<p>
		<input type="hidden"name="setup" value="2" />
		<input type="submit" value="Setup 2: Sending new password for imported users." />
	</p>
	</form>
</div>
<?php
}
else if ( isset($_POST['setup']) && $_POST['setup']=='2' )
{
	echo '<h1>Sending new password email to impored users</h1>';
	
	if ( empty($_POST['from_name']) && empty($_POST['from_email']) )
	{
		$from_email = trim($_POST['from_email']);
	}
	else if ( empty($_POST['from_email']))
	{
		$from_email = 'noreply@example.com';
	}
	else
	{
		$from_email = array
		(
			'name' => trim($_POST['from_name']),
			'email' => trim($_POST['from_email']),
		);
	}
	$subject = trim($_POST['subject']);
	$content = trim($_POST['content']);
	if ( isset($_SESSION['users']) )
	{
		echo 'Starting Sending email...<br /><div class="box">';

		foreach ( $_SESSION['users'] as $user )
		{
			//$content = str_ireplace('{user}', $user['name'], $content);
			//$content = str_ireplace('{password}', $user['password'], $content);
			
			$content = '你好，'.$user['name'].'！<br />也许你还记得曾经注册过 Kohana 中文爱好者论坛的账户，
				目前我们更换了新的论坛系统，由于安全机制的缘故，我们没有办法把用户原先的密码转移过来，
				故重设了所有用户的密码，请放心的是，这都是随机生成并 Email 通过各位用户的，我们也不知道各位的密码是什么。<br />
				下面是你在新论坛系统的相关信息：
				<br />登录地址：<a href="http://forum.khnfans.cn">http://forum.khnfans.cn</a> 或者 
				<a href="http://khnfans.cn/forum">http://khnfans.cn/forum</a>'.
				'<br />登录 Email：'.$user['email'].
				'<br />登录密码：'.$user['password'].
				'<br /><br />我们系统用新的系统，新的管理模式来让大家重新认识我们！期待您的再次到来！';
				
			//echo "<br />===========<br />".$content."<br />=============<br />";
			$result = send_mail($from_email, $user['email'], trim($_POST['subject']), $content, true);
			if ( $result )
			{
				echo '<small class="bold">Send to "'.$user['name'].'" successfu.</small><br />';
			}
			else
			{
				echo '<small class="bold">Send to "'.$user['name'].'" faild.</small><br />';
			}
		}
		echo '</div>Finished Sending email !<br /><br />';
	}
	else
	{
		echo 'unkown errors';
	}
}
else
{
?>
<div class="setting">
	<form method="POST" action="<?php echo $_SERVICE; ?>">
		<p>
			<h1>Discuz 7</h1>
			<input type="text" name="source_host" value="host" />
			<input type="text" name="source_user" value="user" />
			<input type="text" name="source_password" value="password" />
			<input type="text" name="source_database" value="database" />
			<input type="text" name="source_prefix" value="cdb_" />
		</p>
		<p>
			<h1>Babel 0.6</h1>
			<input type="text" name="target_host" value="host" />
			<input type="text" name="target_user" value="user" />
			<input type="text" name="target_password" value="password" />
			<input type="text" name="target_database" value="database" />
			<input type="text" name="target_prefix" value="babel_" />
		</p>
		<p>
			<input type="hidden"name="setup" value="1" />
			<input type="submit" value="Setup 1: Convert Database!" />
		</p>
	</form>
</div>
<?php } ?>
</body>
</html>
	